﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using runtime = Microsoft.SqlServer.Dts.Runtime;
using runtimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using wrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;


namespace BILAB.Technical.History.PackageBuilder
{
    class HistoryExport
    {
        #region members & properties
        private runtime.Package _p;
        private runtime.TaskHost _taskhost;
        private wrapper.MainPipe _mainpipe;
        private List<runtime.ConnectionManager> _soureCMs = new List<Microsoft.SqlServer.Dts.Runtime.ConnectionManager>();
        private List<wrapper.IDTSComponentMetaData100> _oleDBsources = new List<Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100>();
        private List<wrapper.CManagedComponentWrapper> _sourceInstances = new List<Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper>();

        public List<wrapper.CManagedComponentWrapper> SourceInstances
        {
            get { return _sourceInstances; }
            set { _sourceInstances = value; }
        }

        public List<wrapper.IDTSComponentMetaData100> OleDBsources
        {
            get { return _oleDBsources; }
            set { _oleDBsources = value; }
        }

        public List<runtime.ConnectionManager> SoureCMs
        {
            get { return _soureCMs; }
            set { _soureCMs = value; }
        }

        public runtime.Package P
        {
            get { return _p; }
            set { _p = value; }
        }        

        public wrapper.MainPipe Mainpipe
        {
            get { return _mainpipe; }
            set { _mainpipe = value; }
        }
        #endregion

        #region Export Method
        public void CreatePackage(string connectionstring)
        {
            P = new runtime.Package();
            P.Name = "HistoryExport";
            P.CreatorComputerName = "MyPC";
            P.CreatorName = "RHA";

            this.addDataflow();
            this.addSourceCM(connectionstring);
            //this.addSourceConnection();
            //this.addMulticast();
            //this.addPdtSource();
            //this.addSort();
            //this.addMerge();
            //this.addFactDestination();            
        }

        public void SavePackage()
        {
            runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();
            a.SaveToXml("_BILAB.History.dtsx", P, null);
        }
        #endregion

        #region AddSourceCM & AddDataFlow Methods
        private void addSourceCM(string connectionstring)
        {
            // creates a Connection Manager
            runtime.ConnectionManager sourceCM = P.Connections.Add("OLEDB");
            sourceCM.Name = "DatabaseSource";
            sourceCM.ConnectionString = connectionstring;

            // add it to the internal list of Source Connection Manager if many
            SoureCMs.Add(sourceCM);

            
        }
        private void addDataflow()
        {
            /*
             * Common properties of taskhost
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_1devconc/html/1b725922-ec59-4a47-9d55-e079463058f3.htm
             * 
             * Detail :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/e39480e5-613f-4bf4-87b1-249942ed9a35.htm
             */

            /* Add Method
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/a0d4c1cf-d4a3-4297-8892-7b13f235fe9d.htm
             *  
             */
            _taskhost = (runtime.TaskHost)_p.Executables.Add("DTS.Pipeline");
            _taskhost.Name = "DFT History";
            Mainpipe = (wrapper.MainPipe)_taskhost.InnerObject;

        }
        #endregion

        public void InitialiseDataFlow(string rootTable, string rootPrimaryKey, string where,
            string child, string primaryKey)
        {
        }

        private void rootAddParentable(string parentTable,string parentPrimaryKey, string where)
        {
            wrapper.IDTSComponentMetaData100 oledbDatasource = _mainpipe.ComponentMetaDataCollection.New();
            //_oledbDatasource.ComponentClassID = "DTSAdapter.OleDbSource";
            oledbDatasource.ComponentClassID = "DTSAdapter.OLEDBSource";


            // Instanciate an OleDB source Instance for design time based on OleDB Source
            wrapper.CManagedComponentWrapper sourceInstance = oledbDatasource.Instantiate();


            // ProvideComponentProperties acts as a kind of constructor and build input, output ...
            sourceInstance.ProvideComponentProperties();

            // Relink runtime oledb component with the package connection manager defined before
            oledbDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = SoureCMs[0].ID;
            oledbDatasource.RuntimeConnectionCollection[0].ConnectionManager
                            = runtime.DtsConvert.GetExtendedInterface(SoureCMs[0]);

            /* Each dataflow component has properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/065a337e-9a0d-4a5e-aa45-87da58fa37c3.htm
             * 1 - Common Properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/51973502-5cc6-4125-9fce-e60fa1b7b796.htm
             * 2 - Custom Properties for oledb source :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/1361a37f-d647-416b-bb96-542b5258f38d.htm#oledb
             */

            sourceInstance.SetComponentProperty("AccessMode", 2);
            sourceInstance.SetComponentProperty("SqlCommand", "Select * from " + parentTable);

            oledbDatasource.Name = "OLE " + parentTable;           

            sourceInstance.AcquireConnections(null);
            sourceInstance.ReinitializeMetaData();
            sourceInstance.ReleaseConnections();

            OleDBsources.Add(oledbDatasource);
            SourceInstances.Add(sourceInstance);

        }
    }
}
